const sqlite3 = require('sqlite3').verbose();
const path = require('path');

class DatabaseManager {
    constructor() {
        this.db = null;
        this.widgetDatabases = {};
        this.init();
    }

    init() {
        const dbPath = path.join(__dirname, '../data/widgets.db');
        const dataDir = path.dirname(dbPath);
        
        // Ensure data directory exists
        const fs = require('fs');
        if (!fs.existsSync(dataDir)) {
            fs.mkdirSync(dataDir, { recursive: true });
        }
        console.log('数据库文件地址:',dataDir)
        this.db = new sqlite3.Database(dbPath, (err) => {
            if (err) {
                console.error('Error opening database:', err);
            } else {
                console.log('Connected to SQLite database');
                this.createTables();
            }
        });
    }

    async createTables() {
        const createUsersTable = `
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT UNIQUE NOT NULL,
                password_hash TEXT NOT NULL,
                role TEXT NOT NULL DEFAULT 'user',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        `;

        const createSessionsTable = `
            CREATE TABLE IF NOT EXISTS sessions (
                id TEXT PRIMARY KEY,
                user_id INTEGER NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                expires_at DATETIME NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
            )
        `;

        const createWidgetsTable = `
            CREATE TABLE IF NOT EXISTS widgets (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                config TEXT NOT NULL,
                user_id INTEGER NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
            )
        `;

        this.db.run(createUsersTable);
        this.db.run(createSessionsTable);
        this.db.run(createWidgetsTable);
        
        // Initialize widget-specific databases
        await this.initializeWidgetDatabases();
    }

    async initializeWidgetDatabases() {
        try {
            // Load Todo widget database
            const TodoDatabase = require('../widgets/todo/database');
            this.widgetDatabases.todo = new TodoDatabase(this.db);
            await this.widgetDatabases.todo.createTodosTable();
            
            // Load Calendar widget database
            const CalendarDatabase = require('../widgets/calendar/database');
            this.widgetDatabases.calendar = new CalendarDatabase(this.db);
            await this.widgetDatabases.calendar.createEventsTable();
            
            console.log('Widget databases initialized');
        } catch (error) {
            console.error('Error initializing widget databases:', error);
        }
    }

    // Widget operations
    async saveWidget(widget, userId) {
        return new Promise((resolve, reject) => {
            const stmt = this.db.prepare(`
                INSERT OR REPLACE INTO widgets (id, name, config, user_id, updated_at)
                VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)
            `);
            
            stmt.run([widget.id, widget.name, JSON.stringify(widget.config), userId], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.lastID);
                }
            });
            stmt.finalize();
        });
    }

    async getWidgets(userId = null) {
        return new Promise((resolve, reject) => {
            const query = userId 
                ? 'SELECT * FROM widgets WHERE user_id = ? ORDER BY created_at DESC'
                : 'SELECT * FROM widgets ORDER BY created_at DESC';
            const params = userId ? [userId] : [];
            
            this.db.all(query, params, (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    const widgets = rows.map(row => ({
                        id: row.id,
                        name: row.name,
                        config: JSON.parse(row.config),
                        userId: row.user_id,
                        createdAt: row.created_at,
                        updatedAt: row.updated_at
                    }));
                    resolve(widgets);
                }
            });
        });
    }

    async getWidget(id) {
        return new Promise((resolve, reject) => {
            this.db.get('SELECT * FROM widgets WHERE id = ?', [id], (err, row) => {
                if (err) {
                    reject(err);
                } else if (row) {
                    resolve({
                        id: row.id,
                        name: row.name,
                        config: JSON.parse(row.config),
                        userId: row.user_id,
                        createdAt: row.created_at,
                        updatedAt: row.updated_at
                    });
                } else {
                    resolve(null);
                }
            });
        });
    }

    async deleteWidget(id) {
        return new Promise((resolve, reject) => {
            this.db.run('DELETE FROM widgets WHERE id = ?', [id], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.changes > 0);
                }
            });
        });
    }

    // Widget data operations (for storing widget-specific data like todos, events, etc.)
    async saveWidgetData(widgetId, key, value) {
        return new Promise((resolve, reject) => {
            const stmt = this.db.prepare(`
                INSERT OR REPLACE INTO widget_data (widget_id, data_key, data_value, updated_at)
                VALUES (?, ?, ?, CURRENT_TIMESTAMP)
            `);
            
            stmt.run([widgetId, key, JSON.stringify(value)], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.lastID);
                }
            });
            stmt.finalize();
        });
    }

    async getWidgetData(widgetId, key) {
        return new Promise((resolve, reject) => {
            this.db.get(
                'SELECT data_value FROM widget_data WHERE widget_id = ? AND data_key = ?',
                [widgetId, key],
                (err, row) => {
                    if (err) {
                        reject(err);
                    } else if (row) {
                        resolve(JSON.parse(row.data_value));
                    } else {
                        resolve(null);
                    }
                }
            );
        });
    }

    async getAllWidgetData(widgetId) {
        return new Promise((resolve, reject) => {
            this.db.all(
                'SELECT data_key, data_value FROM widget_data WHERE widget_id = ?',
                [widgetId],
                (err, rows) => {
                    if (err) {
                        reject(err);
                    } else {
                        const data = {};
                        rows.forEach(row => {
                            data[row.data_key] = JSON.parse(row.data_value);
                        });
                        resolve(data);
                    }
                }
            );
        });
    }

    async deleteWidgetData(widgetId, key) {
        return new Promise((resolve, reject) => {
            this.db.run(
                'DELETE FROM widget_data WHERE widget_id = ? AND data_key = ?',
                [widgetId, key],
                function(err) {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(this.changes > 0);
                    }
                }
            );
        });
    }

    // User operations
    async createUser(username, password, role = 'user') {
        const bcrypt = require('bcrypt');
        const passwordHash = await bcrypt.hash(password, 10);
        
        return new Promise((resolve, reject) => {
            const stmt = this.db.prepare(`
                INSERT INTO users (username, password_hash, role)
                VALUES (?, ?, ?)
            `);
            
            stmt.run([username, passwordHash, role], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve({
                        id: this.lastID,
                        username,
                        role,
                        createdAt: new Date().toISOString()
                    });
                }
            });
            stmt.finalize();
        });
    }

    async getUserByUsername(username) {
        return new Promise((resolve, reject) => {
            this.db.get('SELECT * FROM users WHERE username = ?', [username], (err, row) => {
                if (err) {
                    reject(err);
                } else if (row) {
                    resolve({
                        id: row.id,
                        username: row.username,
                        passwordHash: row.password_hash,
                        role: row.role,
                        createdAt: row.created_at,
                        updatedAt: row.updated_at
                    });
                } else {
                    resolve(null);
                }
            });
        });
    }

    async getUserById(id) {
        return new Promise((resolve, reject) => {
            this.db.get('SELECT * FROM users WHERE id = ?', [id], (err, row) => {
                if (err) {
                    reject(err);
                } else if (row) {
                    resolve({
                        id: row.id,
                        username: row.username,
                        role: row.role,
                        createdAt: row.created_at,
                        updatedAt: row.updated_at
                    });
                } else {
                    resolve(null);
                }
            });
        });
    }

    async getAllUsers() {
        return new Promise((resolve, reject) => {
            this.db.all('SELECT id, username, role, created_at FROM users ORDER BY created_at DESC', (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    const users = rows.map(row => ({
                        id: row.id,
                        username: row.username,
                        role: row.role,
                        createdAt: row.created_at
                    }));
                    resolve(users);
                }
            });
        });
    }

    async updateUserRole(userId, role) {
        return new Promise((resolve, reject) => {
            this.db.run(
                'UPDATE users SET role = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
                [role, userId],
                function(err) {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(this.changes > 0);
                    }
                }
            );
        });
    }

    async deleteUser(userId) {
        return new Promise((resolve, reject) => {
            this.db.run('DELETE FROM users WHERE id = ?', [userId], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.changes > 0);
                }
            });
        });
    }

    // Session operations
    async createSession(sessionId, userId, expiresAt) {
        return new Promise((resolve, reject) => {
            console.log('DatabaseManager - Creating session:', { sessionId, userId, expiresAt });
            const stmt = this.db.prepare(`
                INSERT INTO sessions (id, user_id, expires_at)
                VALUES (?, ?, ?)
            `);
            
            stmt.run([sessionId, userId, expiresAt], function(err) {
                if (err) {
                    console.error('DatabaseManager - Session creation error:', err);
                    reject(err);
                } else {
                    console.log('DatabaseManager - Session created successfully with ID:', this.lastID);
                    resolve(this.lastID);
                }
            });
            stmt.finalize();
        });
    }

    async getSession(sessionId) {
        return new Promise((resolve, reject) => {
            console.log('DatabaseManager - Getting session for ID:', sessionId);
            this.db.get(
                "SELECT s.*, u.username, u.role FROM sessions s JOIN users u ON s.user_id = u.id WHERE s.id = ? AND s.expires_at > (strftime('%s', 'now') * 1000)",
                [sessionId],
                (err, row) => {
                    if (err) {
                        console.error('DatabaseManager - Session query error:', err);
                        reject(err);
                    } else if (row) {
                        console.log('DatabaseManager - Session found:', row);
                        resolve({
                            id: row.id,
                            userId: row.user_id,
                            username: row.username,
                            role: row.role,
                            createdAt: row.created_at,
                            expiresAt: row.expires_at
                        });
                    } else {
                        console.log('DatabaseManager - No session found for ID:', sessionId);
                        resolve(null);
                    }
                }
            );
        });
    }

    async deleteSession(sessionId) {
        return new Promise((resolve, reject) => {
            this.db.run('DELETE FROM sessions WHERE id = ?', [sessionId], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.changes > 0);
                }
            });
        });
    }

    // Get widget-specific database instance
    getWidgetDatabase(widgetName) {
        return this.widgetDatabases[widgetName] || null;
    }

    close() {
        if (this.db) {
            this.db.close((err) => {
                if (err) {
                    console.error('Error closing database:', err);
                } else {
                    console.log('Database connection closed');
                }
            });
        }
    }
}

module.exports = DatabaseManager;